const mysql = require('mysql2/promise');
require('dotenv').config();

async function testRevenue() {
  console.log('开始检查订单营收数据...\n');

  const connection = await mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME
  });

  try {
    // 1. 查看所有订单
    console.log('=== 1. 所有订单列表 ===');
    const [orders] = await connection.execute(`
      SELECT id, order_no, status, total_amount, actual_amount, created_at
      FROM jd_orders
      ORDER BY created_at DESC
    `);
    
    console.log(`找到 ${orders.length} 个订单：`);
    orders.forEach(order => {
      console.log(`  ID: ${order.id}, 状态: ${order.status}, 总额: ${order.total_amount}, 实付: ${order.actual_amount}`);
    });

    // 2. 统计各状态订单数量
    console.log('\n=== 2. 各状态订单统计 ===');
    const [statusStats] = await connection.execute(`
      SELECT 
        status,
        COUNT(*) as count,
        SUM(actual_amount) as total_amount
      FROM jd_orders
      GROUP BY status
    `);
    
    statusStats.forEach(stat => {
      console.log(`  ${stat.status}: ${stat.count}个订单, 总金额: ¥${stat.total_amount || 0}`);
    });

    // 3. 计算总营收（所有订单）
    console.log('\n=== 3. 总营收统计 ===');
    const [totalStats] = await connection.execute(`
      SELECT 
        COUNT(*) as total,
        SUM(actual_amount) as totalRevenue
      FROM jd_orders
    `);
    
    console.log(`  总订单数: ${totalStats[0].total}`);
    console.log(`  总营收: ¥${totalStats[0].totalRevenue || 0}`);

    // 4. 只计算已支付订单的营收
    console.log('\n=== 4. 已支付订单营收 ===');
    const [paidStats] = await connection.execute(`
      SELECT 
        COUNT(*) as count,
        SUM(actual_amount) as revenue
      FROM jd_orders
      WHERE status IN ('paid', 'shipped', 'completed')
    `);
    
    console.log(`  已支付订单数: ${paidStats[0].count}`);
    console.log(`  已支付营收: ¥${paidStats[0].revenue || 0}`);

    // 5. 查看订单详情
    console.log('\n=== 5. 订单详细信息 ===');
    const [orderDetails] = await connection.execute(`
      SELECT 
        o.id,
        o.order_no,
        o.status,
        o.actual_amount,
        COUNT(oi.id) as item_count,
        SUM(oi.quantity) as total_quantity
      FROM jd_orders o
      LEFT JOIN jd_order_items oi ON o.id = oi.order_id
      GROUP BY o.id
      ORDER BY o.created_at DESC
      LIMIT 5
    `);
    
    console.log('最近5个订单详情：');
    orderDetails.forEach(order => {
      console.log(`  订单${order.id}: ${order.order_no}`);
      console.log(`    状态: ${order.status}, 金额: ¥${order.actual_amount}`);
      console.log(`    商品种类: ${order.item_count}, 总数量: ${order.total_quantity}`);
    });

  } catch (error) {
    console.error('❌ 查询失败:', error.message);
  } finally {
    await connection.end();
  }
}

testRevenue();

